package jsyf.oa.dao.report;

import com.alibaba.fastjson.JSONObject;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by Administrator on 2018/2/24 0024.
 */
@Repository
public class PersonnelDao extends BaseDao {

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return null;
    }

    public JSONObject getGroupByManager(String dataTime) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n ");/*count(1)只扫描主键*/
        sb.append(" (SELECT IFNULL(COUNT(1),0) FROM `t_sys_user` where ENTRY_DATE=" + "'" + dataTime + "')AS entryNumber, \n ");
        sb.append(" (SELECT IFNULL(COUNT(1),0) FROM `t_sys_user_leave` where LEAVE_DATE=" + "'" + dataTime + "')AS leaveNumber; \n ");
        JSONObject jsonObject = super.queryForJsonObject(sb.toString());
        return jsonObject;
    }

    public JSONObject getCompanyByManager(String dataTime, String companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n ");
        sb.append(" (SELECT SUM(targetNumber) from (SELECT targetNumber \n ");
        sb.append(" FROM v_sys_user WHERE frontFlag=1 \n ");
        sb.append(" AND companyId =" + "'" + companyId + "'\n ");
        sb.append(" AND delFlag = 0 \n ");
        sb.append(" GROUP BY roleId) as targetNumber) AS standard,\n ");
        sb.append(" (SELECT COUNT(1) FROM v_sys_user WHERE companyId=" + "'" + companyId + "' AND frontFlag=1 AND delFlag=0) AS frontNumber,\n\n ");
        sb.append(" (SELECT IFNULL(COUNT(1),0) FROM `v_sys_user` where entryDate=" + "'" + dataTime + "'  AND companyId=" + "'" + companyId + "')AS entryNumber,\n ");
        sb.append(" (SELECT COUNT(1) FROM `t_sys_user_leave` where LEAVE_DATE=" + "'" + dataTime + "' AND COMPANY_ID=" + "'" + companyId + "')AS leaveNumber; ");
        JSONObject jsonObject = super.queryForJsonObject(sb.toString());
        return jsonObject;
    }

    public JSONObject getRegionByManager(String dataTime, String userId, String departId) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n ");
        sb.append(" (SELECT SUM(targetNumber) from (SELECT targetNumber \n ");
        sb.append(" FROM v_sys_user WHERE frontFlag=1 \n ");
        sb.append(" AND parentId =" + "'" + userId + "'\n ");
        sb.append(" AND delFlag = 0 \n ");
        sb.append(" GROUP BY roleId) as targetNumber) AS standard,\n ");
        sb.append(" (SELECT COUNT(1) FROM v_sys_user WHERE parentId=" + "'" + userId + "' AND frontFlag=1 AND delFlag=0) AS frontNumber,\n");
        sb.append(" (SELECT IFNULL(COUNT(1),0) FROM `v_sys_user` where entryDate=" + "'" + dataTime + "'  AND departId=" + "'" + departId + "')AS entryNumber,\n ");
        sb.append(" (SELECT COUNT(1) FROM `t_sys_user_leave` \n");
        sb.append(" LEFT JOIN v_sys_user ON t_sys_user_leave.USER_ID=v_sys_user.id \n");
        sb.append(" where LEAVE_DATE=" + "'" + dataTime + "' \n");
        sb.append(" AND v_sys_user.departId=" + "'" + departId + "' ");
        sb.append(" )AS leaveNumber; ");
        JSONObject jsonObject = super.queryForJsonObject(sb.toString());
        return jsonObject;
    }

    public List<JSONObject> getCompanyByManage() {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n ");
        sb.append(" company.ID AS companyId,\n");
        sb.append(" company.`NAME` AS companyName,\n ");
        sb.append(" roleName.`NAME` AS roleName,\n ");
        sb.append(" SUM(role.TARGET_NUMBER) AS targetNumber,\n ");
        sb.append(" (select COUNT(1) FROM v_sys_user WHERE companyId=company.ID AND roleNameId=roleName.ID and delFlag=0 AND jobState!=1) AS nowNumber,\n ");
        sb.append(" CASE WHEN (SELECT SUM(role.TARGET_NUMBER)-(select COUNT(1) FROM v_sys_user WHERE companyId=company.ID AND roleNameId=roleName.ID and delFlag=0 AND jobState!=1)) > 0 THEN (SELECT SUM(role.TARGET_NUMBER)-(select COUNT(1) FROM v_sys_user WHERE companyId=company.ID AND roleNameId=roleName.ID and delFlag=0 AND jobState!=1)) ELSE 0 END as vacancy \n ");
        sb.append(" FROM  \n ");
        sb.append(" t_sys_company AS company\n ");
        sb.append(" LEFT JOIN t_sys_depart AS depart ON company.ID = depart.COMPANY_ID \n ");
        sb.append(" LEFT JOIN t_sys_role AS role ON depart.ID = role.DEPART_ID \n ");
        sb.append(" LEFT JOIN t_sys_role_name AS roleName ON role.ROLE_NAME_ID = roleName.ID \n ");
        sb.append(" WHERE  \n ");
        sb.append(" company.PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA' \n ");
        sb.append(" AND company.`NAME` NOT LIKE '%大客户%' \n ");
        sb.append(" AND roleName.FRONT_FLAG=1\n ");
        sb.append(" AND company.DEL_FLAG = 0 \n ");
        sb.append(" AND depart.DEL_FLAG=0\n");
        sb.append(" AND role.DEL_FLAG=0 \n");
        sb.append(" AND roleName.DEL_FLAG=0 \n");
        sb.append(" GROUP BY company.ID,roleName.ID\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getSaleByManage(String companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n ");
        sb.append(" depart.ID AS departId,\n");
        sb.append(" REPLACE(depart.`NAME`, '大区', '') AS departName,\n ");
        sb.append(" roleName.`NAME` AS roleName,\n ");
        sb.append(" SUM(role.TARGET_NUMBER) AS targetNumber,\n ");
        sb.append(" (select COUNT(1) FROM v_sys_user WHERE departId=depart.ID AND roleNameId=roleName.ID and delFlag=0 AND jobState!=1) AS nowNumber,\n ");
        sb.append(" CASE WHEN (SELECT SUM(role.TARGET_NUMBER)-(select COUNT(1) FROM v_sys_user WHERE departId=depart.ID AND roleNameId=roleName.ID and delFlag=0 AND jobState!=1))> 0 THEN (SELECT SUM(role.TARGET_NUMBER)-(select COUNT(1) FROM v_sys_user WHERE departId=depart.ID AND roleNameId=roleName.ID and delFlag=0 AND jobState!=1)) ELSE 0 END as vacancy \n ");
        sb.append(" FROM  \n ");
        sb.append(" t_sys_depart AS depart\n ");
        sb.append(" LEFT JOIN t_sys_role AS role ON depart.ID = role.DEPART_ID \n ");
        sb.append(" LEFT JOIN t_sys_role_name AS roleName ON role.ROLE_NAME_ID = roleName.ID \n ");
        sb.append(" WHERE  \n ");
        sb.append(" depart.COMPANY_ID = " + "'" + companyId + "' \n ");
        sb.append(" AND roleName.FRONT_FLAG=1\n ");
        sb.append(" AND depart.DEL_FLAG=0\n");
        sb.append(" AND role.DEL_FLAG=0 \n");
        sb.append(" AND roleName.DEL_FLAG=0 \n");
        sb.append(" GROUP BY depart.ID,roleName.ID\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    /*入职人员信息*/
    public List<JSONObject> getGroupByManagerEntry(String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT v_sys_user.name,v_sys_user.tel,v_sys_user.companyName,v_sys_user.roleName  FROM `v_sys_user` where entryDate=" + "'" + dateTime + "'");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getCompanyByManagerEntry(String dateTime, String companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT v_sys_user.name,v_sys_user.tel,v_sys_user.companyName,v_sys_user.roleName FROM v_sys_user WHERE entryDate=" + "'" + dateTime + "'AND companyId=" + "'" + companyId + "'");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getRegionByManagerEntry(String dateTime, String userId, String departId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT v_sys_user.name,v_sys_user.tel,v_sys_user.companyName,v_sys_user.roleName FROM `v_sys_user` where departId=" + "'" + departId + "'AND entryDate="+"'"+dateTime+"'");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }
/*离职人员信息*/
public List<JSONObject> getGroupByManagerLea(String dateTime) {
    StringBuffer sb = new StringBuffer();
    sb.append("SELECT v_sys_user.name,v_sys_user.tel,v_sys_user.companyName,v_sys_user.roleName  FROM `v_sys_user` where leaveDate=" + "'"+dateTime+"' and jobState=1  ");
    List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
    return jsonList;
}

    public List<JSONObject> getCompanyByManagerLea(String dateTime, String companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT v_sys_user.name,v_sys_user.tel,v_sys_user.companyName,v_sys_user.roleName FROM v_sys_user WHERE companyId=" + "'" + companyId + "'AND leaveDate="+ "'"+dateTime+"' and jobState=1 ");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getRegionByManagerLea(String dateTime,String userId, String departId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  v_sys_user.name,v_sys_user.tel,v_sys_user.companyName,v_sys_user.roleName FROM `t_sys_user_leave` ");
        sb.append(" LEFT JOIN v_sys_user ON t_sys_user_leave.USER_ID=v_sys_user.id \n");
       // sb.append(" where LEAVE_DATE=" + "'" + dateTime + "' \n");
        sb.append(" where v_sys_user.departId=" + "'" + departId + "' AND v_sys_user.jobState=1   AND v_sys_user.leaveDate= "+ "'"+dateTime+"'");

        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }




}
